package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.dao.BaseDao;
import com.edu.dao.CollectionDao;
import com.edu.entity.goodsCollection;

public class collectionDaoImpl implements CollectionDao {
	BaseDao bd = new BaseDao();
	@Override
	public List<goodsCollection> getGoods(int userId) {
		 List<goodsCollection> result = new ArrayList<>();
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs = null;
	        
	        try {
	            // 获取数据库连接
	            conn = bd.getConn();
	            
	            
	            // SQL查询：先获取用户的收藏记录，再关联商品信息
	            String sql = "SELECT g.*, c.collectionTime " +
	                         "FROM myCollection c " +
	                         "JOIN goods g ON c.goodsid = g.goodid " +
	                         "WHERE c.userid = ? " +
	                         "ORDER BY c.collectionTime DESC ";
	            
	            pstmt = conn.prepareStatement(sql);
	            pstmt.setInt(1, userId);
	            
	            rs = pstmt.executeQuery();
	            
	            // 遍历结果集，构建DTO对象
	            while (rs.next()) {
	            	goodsCollection goodscollection = new goodsCollection();
	                
	                // 设置商品基本信息
	            	goodscollection.setGoodid(rs.getInt("goodid"));
	            	goodscollection.setGoodname(rs.getString("goodname"));
	            	goodscollection.setGoodprice(rs.getDouble("goodprice"));
	            	goodscollection.setGoodimg(rs.getString("goodimg"));
	            	goodscollection.setGoodstat(rs.getInt("goodstat"));
	            	goodscollection.setSellerId(rs.getInt("sellerid"));
	                // 设置其他商品字段...
	                
	                // 设置收藏时间
	            	goodscollection.setCollectionTime(rs.getTimestamp("collectionTime"));
	                
	                result.add(goodscollection);
	            }
	            
	        } catch (SQLException e) {
	            e.printStackTrace();
	            // 实际项目中应该处理异常或抛出
	        } finally {
	            // 关闭资源
	        	bd.closeAll(conn, pstmt, rs);
	        }
	        
	        return result;
	}
	@Override
	public int getCollectionAll(int userId) {
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    int totalCount = 0; // 默认返回0
	    try {
	        // 获取数据库连接
	        conn = bd.getConn();
	        
	        // SQL查询：统计指定用户的收藏商品总数
	        String sql = "SELECT COUNT(*) AS total FROM myCollection WHERE userid = ?";
	        
	        pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1, userId);
	        
	        rs = pstmt.executeQuery();
	        
	        // 获取结果
	        if (rs.next()) {
	            totalCount = rs.getInt("total");
	        }
	        
	    } catch (SQLException e) {
	        e.printStackTrace();
	        // 实际项目中应该处理异常或抛出
	    } finally {
	        // 关闭资源
	    	bd.closeAll(conn, pstmt, rs);
	    }
	    
	    return totalCount;
	}
	@Override
	public boolean deleteCollection(int userId, int goodsId) {
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    try {
	        conn = bd.getConn();
	        String sql = "DELETE FROM myCollection WHERE userid = ? AND goodsid = ?";
	        pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1, userId);
	        pstmt.setInt(2, goodsId);
	        System.out.println(sql);
	        int rows = pstmt.executeUpdate();
	        return rows > 0;
	    } catch (SQLException e) {
	        e.printStackTrace();
	        return false;
	    } finally {
	        bd.closeAll(conn, pstmt, null);
	    }
	}
	@Override
	public int sqlAddCollection(int userId, int goodsId) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO `mycollection` (`userid`, `goodsid`, `collectionTime`) VALUES (?, ?, NOW())";
		Object[] obj = {userId,goodsId};
		return bd.executeUpdate(sql, obj);
	}
	@Override
	public goodsCollection sqlFindColl(int userId, int goodsId) {
		// TODO Auto-generated method stub
		String sql = "select * from mycollection where userid = ? and goodsid = ?";
		Connection conn = bd.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		goodsCollection gc = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, goodsId);
			System.out.println(ps);
			rs = ps.executeQuery();
			while(rs.next()) {
				gc = new goodsCollection();
				gc.setGoodid(rs.getInt("goodsid"));
				gc.setCollUserid(rs.getInt("userid"));
			}
			return gc;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			bd.closeAll(conn, ps, rs);
		}
		return gc;
	}

	

}
